package com.learning.railwayreservation.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.learning.railwayreservation.vo.CheckAvailibility2VO;
import com.learning.railwayreservation.vo.CheckAvailibilityVO;

public class CheckAvailibilityDao extends BaseDao {
	public static void main(String[] args) {

		CheckAvailibilityDao cAdao = new CheckAvailibilityDao();
		List<CheckAvailibilityVO> checkAvailibilityList = cAdao.getResult();
		for (CheckAvailibilityVO checkAvailibilityVO : checkAvailibilityList) {

			System.out.print("\nTrain Name:"
					+ checkAvailibilityVO.getTrainName());
			System.out.print("....     Seat id:"
					+ checkAvailibilityVO.getSeatId());
			System.out.print("....     Boggie id:"
					+ checkAvailibilityVO.getBoggieId());
			System.out.print("\n");

		}
	}

	public List<CheckAvailibilityVO> getResult() {

		CheckAvailibilityDao tdao = new CheckAvailibilityDao();
		Connection conn = tdao.getConnection();
		List<CheckAvailibilityVO> checkAvailibilityList = new ArrayList<CheckAvailibilityVO>();
		CheckAvailibilityVO checkAvailibilityVO;

		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT seat.seat_id , seat.boggie_id , master_train.train_name , master_train.route_id , seat.vacant_or_reserved FROM seat , train  , master_train WHERE train.master_train_id= master_train.master_train_id AND master_train.master_train_id = 1 AND train.start_date = To_Date('03-May-2003', 'DD-Mon-YYYY')AND seat.vacant_or_reserved = 'VACANT'");

			while (rs.next()) {
				checkAvailibilityVO = new CheckAvailibilityVO();
				checkAvailibilityVO.setSeatId(rs.getInt("SEAT_ID"));
				checkAvailibilityVO.setBoggieId(rs.getInt("BOGGIE_ID"));
				checkAvailibilityVO.setTrainName(rs.getString("TRAIN_NAME"));

			}
		}

		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return checkAvailibilityList;
	}

	public List<CheckAvailibility2VO> getAvailability(
			CheckAvailibility2VO checkAvailVO) {

		CheckAvailibilityDao tdao = new CheckAvailibilityDao();
		Connection conn = tdao.getConnection();
		List<CheckAvailibility2VO> checkAvailibilityList = new ArrayList<CheckAvailibility2VO>();

		try {
			String sql = "SELECT seat.seat_id ,seat.row_id, seat.boggie_id , master_train.train_name , master_train.route_id , "
					+ "seat.vacant_or_reserved , boggie.boggie_type FROM seat , train  , master_train , boggie "
					+ "WHERE train.master_train_id= master_train.master_train_id "
					+ "AND master_train.master_train_id = ? "
					+ "AND train.start_date = ?"
					+ "AND seat.vacant_or_reserved = 'VACANT'"
			        + "AND boggie.boggie_type = ?";

			PreparedStatement stmt = conn.prepareStatement(sql);

			stmt.setInt(1, checkAvailVO.getTrainNumber());
			stmt.setDate(2, checkAvailVO.getDateOfJourney());
			stmt.setString(3, checkAvailVO.getClassType());
			
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				System.out.print(rs.getInt("seat_id"));
				System.out.print(rs.getString("row_id"));
				System.out.print("   ");
				System.out.print(rs.getInt("boggie_id"));
				System.out.print("   ");
				System.out.print(rs.getString("boggie_type"));
				System.out.print("   ");
				System.out.println(rs.getString("train_name"));
				// checkAvailibilityList.add(checkAvailVO);
			}
		}

		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return checkAvailibilityList;
	}

}
